{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# 天池o2o优惠券使用预测比赛解析（进阶）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**赛题链接：**\n",
    "\n",
    "[天池o2o优惠券使用预测](https://tianchi.aliyun.com/getStart/introduction.htm?spm=5176.100066.0.0.518433afBqXIKM&raceId=231593)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入相关库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# import libraries necessary for this project\n",
    "import os, sys, pickle\n",
    " \n",
    "import numpy as np\n",
    "import pandas as pd\n",
    " \n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.dates as mdates\n",
    " \n",
    "import seaborn as sns\n",
    " \n",
    "from datetime import date\n",
    " \n",
    "from sklearn.model_selection import KFold, train_test_split, StratifiedKFold, cross_val_score, GridSearchCV\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.linear_model import SGDClassifier, LogisticRegression\n",
    "from sklearn.preprocessing import StandardScaler\n",
    "from sklearn.metrics import log_loss, roc_auc_score, auc, roc_curve\n",
    "from sklearn.preprocessing import MinMaxScaler\n",
    " \n",
    "import xgboost as xgb        # pip install xgboost-0.80-py2.py3-none-win_amd64.whl    https://pypi.org/project/xgboost/#files\n",
    "import lightgbm as lgb       # pip install lightgbm-2.1.2-py2.py3-none-win_amd64.whl  https://pypi.org/project/lightgbm/#files\n",
    " \n",
    "# display for this notebook\n",
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format = 'retina'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 导入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date  \n",
       "0  20160217  \n",
       "1      null  \n",
       "2      null  \n",
       "3      null  \n",
       "4      null  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff = pd.read_csv('data/ccf_offline_stage1_train.csv')\n",
    "dfon = pd.read_csv('data/ccf_online_stage1_train.csv')\n",
    "dftest = pd.read_csv('data/ccf_offline_stage1_test_revised.csv')\n",
    " \n",
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 简单统计"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "简单统计一下用户使用优惠券的情况："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "有优惠卷，购买商品：75382\n",
      "有优惠卷，未购商品：977900\n",
      "无优惠卷，购买商品：701602\n",
      "无优惠卷，未购商品：0\n"
     ]
    }
   ],
   "source": [
    "print('有优惠卷，购买商品：%d' % dfoff[(dfoff['Date_received'] != 'null') & (dfoff['Date'] != 'null')].shape[0])\n",
    "print('有优惠卷，未购商品：%d' % dfoff[(dfoff['Date_received'] != 'null') & (dfoff['Date'] == 'null')].shape[0])\n",
    "print('无优惠卷，购买商品：%d' % dfoff[(dfoff['Date_received'] == 'null') & (dfoff['Date'] != 'null')].shape[0])\n",
    "print('无优惠卷，未购商品：%d' % dfoff[(dfoff['Date_received'] == 'null') & (dfoff['Date'] == 'null')].shape[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "可见，很多人（701602）购买商品却没有使用优惠券，也有很多人（977900）有优惠券但却没有使用，真正使用优惠券购买商品的人（75382）很少！所以，这个比赛的意义就是把优惠券送给真正可能会购买商品的人。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 特征提取"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date  \n",
       "0  20160217  \n",
       "1      null  \n",
       "2      null  \n",
       "3      null  \n",
       "4      null  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 打折率 Discount_rate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Discount_rate 类型：\n",
      " ['null' '150:20' '20:1' '200:20' '30:5' '50:10' '10:5' '100:10' '200:30'\n",
      " '20:5' '30:10' '50:5' '150:10' '100:30' '200:50' '100:50' '300:30' '50:20'\n",
      " '0.9' '10:1' '30:1' '0.95' '100:5' '5:1' '100:20' '0.8' '50:1' '200:10'\n",
      " '300:20' '100:1' '150:30' '300:50' '20:10' '0.85' '0.6' '150:50' '0.75'\n",
      " '0.5' '200:5' '0.7' '30:20' '300:10' '0.2' '50:30' '200:100' '150:5']\n"
     ]
    }
   ],
   "source": [
    "print('Discount_rate 类型：\\n',dfoff['Discount_rate'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "打折率分为 3 种情况：\n",
    "\n",
    "- 'null' 表示没有打折\n",
    "\n",
    "- [0,1] 表示折扣率\n",
    "\n",
    "- x:y 表示满x减y"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**处理方式：**\n",
    "\n",
    "- 打折类型：getDiscountType()\n",
    "\n",
    "- 折扣率：convertRate()\n",
    "\n",
    "- 满多少：getDiscountMan()\n",
    "\n",
    "- 减多少：getDiscountJian()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Convert Discount_rate and Distance\n",
    "def getDiscountType(row):\n",
    "    if row == 'null':\n",
    "        return 'null'\n",
    "    elif ':' in row:\n",
    "        return 1\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "def convertRate(row):\n",
    "    \"\"\"Convert discount to rate\"\"\"\n",
    "    if row == 'null':\n",
    "        return 1.0\n",
    "    elif ':' in row:\n",
    "        rows = row.split(':')\n",
    "        return 1.0 - float(rows[1])/float(rows[0])\n",
    "    else:\n",
    "        return float(row)\n",
    "    \n",
    "def getDiscountMan(row):\n",
    "    if ':' in row:\n",
    "        rows = row.split(':')\n",
    "        return int(rows[0])\n",
    "    else:\n",
    "        return 0\n",
    "\n",
    "def getDiscountJian(row):\n",
    "    if ':' in row:\n",
    "        rows = row.split(':')\n",
    "        return int(rows[1])\n",
    "    else:\n",
    "        return 0\n",
    "    \n",
    "def processData(df):\n",
    "    \n",
    "    # convert discount_rate\n",
    "    df['discount_type'] = df['Discount_rate'].apply(getDiscountType)\n",
    "    df['discount_rate'] = df['Discount_rate'].apply(convertRate)\n",
    "    df['discount_man'] = df['Discount_rate'].apply(getDiscountMan)\n",
    "    df['discount_jian'] = df['Discount_rate'].apply(getDiscountJian)\n",
    "    \n",
    "    print(df['discount_rate'].unique())\n",
    "    \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 1.          0.86666667  0.95        0.9         0.83333333  0.8         0.5\n",
      "  0.85        0.75        0.66666667  0.93333333  0.7         0.6\n",
      "  0.96666667  0.98        0.99        0.975       0.33333333  0.2         0.4       ]\n",
      "[ 0.83333333  0.9         0.96666667  0.8         0.95        0.75        0.98\n",
      "  0.5         0.86666667  0.6         0.66666667  0.7         0.85\n",
      "  0.33333333  0.94        0.93333333  0.975       0.99      ]\n"
     ]
    }
   ],
   "source": [
    "dfoff = processData(dfoff)\n",
    "dftest = processData(dftest)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>discount_jian</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>20</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date discount_type  discount_rate  discount_man  discount_jian  \n",
       "0  20160217          null       1.000000             0              0  \n",
       "1      null             1       0.866667           150             20  \n",
       "2      null             1       0.950000            20              1  \n",
       "3      null             1       0.950000            20              1  \n",
       "4      null             1       0.950000            20              1  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 距离 Distance"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distance 类型： ['0' '1' 'null' '2' '10' '4' '7' '9' '3' '5' '6' '8']\n"
     ]
    }
   ],
   "source": [
    "print('Distance 类型：',dfoff['Distance'].unique())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "将距离 str 转为 int。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[ 0  1 -1  2 10  4  7  9  3  5  6  8]\n",
      "[ 1 -1  5  2  0 10  3  6  7  4  9  8]\n"
     ]
    }
   ],
   "source": [
    "# convert distance\n",
    "dfoff['distance'] = dfoff['Distance'].replace('null', -1).astype(int)\n",
    "print(dfoff['distance'].unique())\n",
    "dftest['distance'] = dftest['Distance'].replace('null', -1).astype(int)\n",
    "print(dftest['distance'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>discount_jian</th>\n",
       "      <th>distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date discount_type  discount_rate  discount_man  discount_jian  \\\n",
       "0  20160217          null       1.000000             0              0   \n",
       "1      null             1       0.866667           150             20   \n",
       "2      null             1       0.950000            20              1   \n",
       "3      null             1       0.950000            20              1   \n",
       "4      null             1       0.950000            20              1   \n",
       "\n",
       "   distance  \n",
       "0         0  \n",
       "1         1  \n",
       "2         0  \n",
       "3         0  \n",
       "4         0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 领劵日期 Date_received"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "优惠卷收到日期从 20160101 到 20160615\n",
      "消费日期从 20160101 到 20160630\n"
     ]
    }
   ],
   "source": [
    "date_received = dfoff['Date_received'].unique()\n",
    "date_received = sorted(date_received[date_received != 'null'])\n",
    "\n",
    "date_buy = dfoff['Date'].unique()\n",
    "date_buy = sorted(date_buy[date_buy != 'null'])\n",
    "\n",
    "print('优惠卷收到日期从',date_received[0],'到',date_received[-1])\n",
    "print('消费日期从',date_buy[0],'到',date_buy[-1])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**关于领劵日期的特征：**\n",
    "\n",
    "- weekday : {null, 1, 2, 3, 4, 5, 6, 7}\n",
    "\n",
    "- weekday_type : {1, 0}（周六和周日为1，其他为0）\n",
    "\n",
    "- Weekday_1 : {1, 0, 0, 0, 0, 0, 0}\n",
    "\n",
    "- Weekday_2 : {0, 1, 0, 0, 0, 0, 0}\n",
    "\n",
    "- Weekday_3 : {0, 0, 1, 0, 0, 0, 0}\n",
    "\n",
    "- Weekday_4 : {0, 0, 0, 1, 0, 0, 0}\n",
    "\n",
    "- Weekday_5 : {0, 0, 0, 0, 1, 0, 0}\n",
    "\n",
    "- Weekday_6 : {0, 0, 0, 0, 0, 1, 0}\n",
    "\n",
    "- Weekday_7 : {0, 0, 0, 0, 0, 0, 1}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def getWeekday(row):\n",
    "    if row == 'null':\n",
    "        return row\n",
    "    else:\n",
    "        return date(int(row[0:4]), int(row[4:6]), int(row[6:8])).weekday() + 1\n",
    "\n",
    "dfoff['weekday'] = dfoff['Date_received'].astype(str).apply(getWeekday)\n",
    "dftest['weekday'] = dftest['Date_received'].astype(str).apply(getWeekday)\n",
    "\n",
    "# weekday_type :  周六和周日为1，其他为0\n",
    "dfoff['weekday_type'] = dfoff['weekday'].apply(lambda x: 1 if x in [6,7] else 0)\n",
    "dftest['weekday_type'] = dftest['weekday'].apply(lambda x: 1 if x in [6,7] else 0)\n",
    "\n",
    "# change weekday to one-hot encoding \n",
    "weekdaycols = ['weekday_' + str(i) for i in range(1,8)]\n",
    "#print(weekdaycols)\n",
    "\n",
    "tmpdf = pd.get_dummies(dfoff['weekday'].replace('null', np.nan))\n",
    "tmpdf.columns = weekdaycols\n",
    "dfoff[weekdaycols] = tmpdf\n",
    "\n",
    "tmpdf = pd.get_dummies(dftest['weekday'].replace('null', np.nan))\n",
    "tmpdf.columns = weekdaycols\n",
    "dftest[weekdaycols] = tmpdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>distance</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weekday_type</th>\n",
       "      <th>weekday_1</th>\n",
       "      <th>weekday_2</th>\n",
       "      <th>weekday_3</th>\n",
       "      <th>weekday_4</th>\n",
       "      <th>weekday_5</th>\n",
       "      <th>weekday_6</th>\n",
       "      <th>weekday_7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date discount_type  discount_rate  discount_man    ...      distance  \\\n",
       "0  20160217          null       1.000000             0    ...             0   \n",
       "1      null             1       0.866667           150    ...             1   \n",
       "2      null             1       0.950000            20    ...             0   \n",
       "3      null             1       0.950000            20    ...             0   \n",
       "4      null             1       0.950000            20    ...             0   \n",
       "\n",
       "   weekday weekday_type  weekday_1  weekday_2  weekday_3  weekday_4  \\\n",
       "0     null            0          0          0          0          0   \n",
       "1        6            1          0          0          0          0   \n",
       "2        3            0          0          0          1          0   \n",
       "3        6            1          0          0          0          0   \n",
       "4        1            0          1          0          0          0   \n",
       "\n",
       "   weekday_5  weekday_6  weekday_7  \n",
       "0          0          0          0  \n",
       "1          0          1          0  \n",
       "2          0          0          0  \n",
       "3          0          1          0  \n",
       "4          0          0          0  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 所有特征："
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- discount_rate\n",
    "\n",
    "- discount_type\n",
    "\n",
    "- discount_man\n",
    "\n",
    "- discount_jian\n",
    "\n",
    "- distance\n",
    "\n",
    "- weekday\n",
    "\n",
    "- weekday_type\n",
    "\n",
    "- weekday_1\n",
    "\n",
    "- weekday_2\n",
    "\n",
    "- weekday_3\n",
    "\n",
    "- weekday_4\n",
    "\n",
    "- weekday_5\n",
    "\n",
    "- weekday_6\n",
    "\n",
    "- weekday_7"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 标签标注"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "三种情况：\n",
    "\n",
    "- Date_received == 'null'：表示没有领到优惠券，无需考虑，y = -1\n",
    "\n",
    "- (Date_received != 'null') & (Date != 'null') & (Date - Date_received <= 15)：表示领取优惠券且在15天内使用，即正样本，y = 1\n",
    "\n",
    "- (Date_received != 'null') & ((Date == 'null') | (Date - Date_received > 15))：表示领取优惠券未在在15天内使用，即负样本，y = 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "定义标签备注函数："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def label(row):\n",
    "    if row['Date_received'] == 'null':\n",
    "        return -1\n",
    "    if row['Date'] != 'null':\n",
    "        td = pd.to_datetime(row['Date'], format='%Y%m%d') - pd.to_datetime(row['Date_received'], format='%Y%m%d')\n",
    "        if td <= pd.Timedelta(15, 'D'):\n",
    "            return 1\n",
    "    return 0\n",
    "\n",
    "dfoff['label'] = dfoff.apply(label, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " 0    988887\n",
      "-1    701602\n",
      " 1     64395\n",
      "Name: label, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "print(dfoff['label'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>weekday</th>\n",
       "      <th>weekday_type</th>\n",
       "      <th>weekday_1</th>\n",
       "      <th>weekday_2</th>\n",
       "      <th>weekday_3</th>\n",
       "      <th>weekday_4</th>\n",
       "      <th>weekday_5</th>\n",
       "      <th>weekday_6</th>\n",
       "      <th>weekday_7</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>null</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>null</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0</td>\n",
       "      <td>...</td>\n",
       "      <td>null</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160217</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>1078</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160319</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 22 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         2632      null          null        0          null   \n",
       "1  1439408         4663     11002        150:20        1      20160528   \n",
       "2  1439408         2632      8591          20:1        0      20160217   \n",
       "3  1439408         2632      1078          20:1        0      20160319   \n",
       "4  1439408         2632      8591          20:1        0      20160613   \n",
       "\n",
       "       Date discount_type  discount_rate  discount_man  ...    weekday  \\\n",
       "0  20160217          null       1.000000             0  ...       null   \n",
       "1      null             1       0.866667           150  ...          6   \n",
       "2      null             1       0.950000            20  ...          3   \n",
       "3      null             1       0.950000            20  ...          6   \n",
       "4      null             1       0.950000            20  ...          1   \n",
       "\n",
       "   weekday_type weekday_1  weekday_2  weekday_3  weekday_4  weekday_5  \\\n",
       "0             0         0          0          0          0          0   \n",
       "1             1         0          0          0          0          0   \n",
       "2             0         0          0          1          0          0   \n",
       "3             1         0          0          0          0          0   \n",
       "4             0         1          0          0          0          0   \n",
       "\n",
       "   weekday_6  weekday_7  label  \n",
       "0          0          0     -1  \n",
       "1          1          0      0  \n",
       "2          0          0      0  \n",
       "3          1          0      0  \n",
       "4          0          0      0  \n",
       "\n",
       "[5 rows x 22 columns]"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dfoff.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 建立线性模型 SGDClassifier"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- 使用上面提取的14个特征。\n",
    "\n",
    "- 训练集：20160101-20160515；验证集：20160516-20160615。\n",
    "\n",
    "- 用线性模型 SGDClassifier"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 划分训练集/验证集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Train Set: \n",
      " 0    759172\n",
      "1     41524\n",
      "Name: label, dtype: int64\n",
      "Valid Set: \n",
      " 0    229715\n",
      "1     22871\n",
      "Name: label, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "# data split\n",
    "df = dfoff[dfoff['label'] != -1].copy()\n",
    "train = df[(df['Date_received'] < '20160516')].copy()\n",
    "valid = df[(df['Date_received'] >= '20160516') & (df['Date_received'] <= '20160615')].copy()\n",
    "print('Train Set: \\n', train['label'].value_counts())\n",
    "print('Valid Set: \\n', valid['label'].value_counts())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "共有特征： 14 个\n",
      "['discount_rate', 'discount_type', 'discount_man', 'discount_jian', 'distance', 'weekday', 'weekday_type', 'weekday_1', 'weekday_2', 'weekday_3', 'weekday_4', 'weekday_5', 'weekday_6', 'weekday_7']\n"
     ]
    }
   ],
   "source": [
    "# feature\n",
    "original_feature = ['discount_rate','discount_type','discount_man', 'discount_jian','distance', 'weekday', 'weekday_type'] + weekdaycols\n",
    "print('共有特征：',len(original_feature),'个')\n",
    "print(original_feature)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 建立模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def check_model(data, predictors):\n",
    "    \n",
    "    classifier = lambda: SGDClassifier(\n",
    "        loss='log',  # loss function: logistic regression\n",
    "        penalty='elasticnet', # L1 & L2\n",
    "        fit_intercept=True,  # 是否存在截距，默认存在\n",
    "        max_iter=100, \n",
    "        shuffle=True,  # Whether or not the training data should be shuffled after each epoch\n",
    "        n_jobs=1, # The number of processors to use\n",
    "        class_weight=None) # Weights associated with classes. If not given, all classes are supposed to have weight one.\n",
    " \n",
    "    # 管道机制使得参数集在新数据集（比如测试集）上的重复使用，管道机制实现了对全部步骤的流式化封装和管理。\n",
    "    model = Pipeline(steps=[\n",
    "        ('ss', StandardScaler()), # transformer\n",
    "        ('en', classifier())  # estimator\n",
    "    ])\n",
    " \n",
    "    parameters = {\n",
    "        'en__alpha': [ 0.001, 0.01, 0.1],\n",
    "        'en__l1_ratio': [ 0.001, 0.01, 0.1]\n",
    "    }\n",
    " \n",
    "    # StratifiedKFold用法类似Kfold，但是他是分层采样，确保训练集，测试集中各类别样本的比例与原始数据集中相同。\n",
    "    folder = StratifiedKFold(n_splits=3, shuffle=True)\n",
    "    \n",
    "    # Exhaustive search over specified parameter values for an estimator.\n",
    "    grid_search = GridSearchCV(\n",
    "        model, \n",
    "        parameters, \n",
    "        cv=folder, \n",
    "        n_jobs=-1,  # -1 means using all processors\n",
    "        verbose=1)\n",
    "    grid_search = grid_search.fit(data[predictors], \n",
    "                                  data['label'])\n",
    "    \n",
    "    return grid_search"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fitting 3 folds for each of 9 candidates, totalling 27 fits\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=-1)]: Done  27 out of  27 | elapsed:  2.4min finished\n"
     ]
    }
   ],
   "source": [
    "predictors = original_feature\n",
    "model = check_model(train, predictors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 验证"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对验证集中每个优惠券预测的结果计算 AUC，再对所有优惠券的 AUC 求平均。计算 AUC 的时候，如果 label 只有一类，就直接跳过，因为 AUC 无法计算。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>weekday_type</th>\n",
       "      <th>weekday_1</th>\n",
       "      <th>weekday_2</th>\n",
       "      <th>weekday_3</th>\n",
       "      <th>weekday_4</th>\n",
       "      <th>weekday_5</th>\n",
       "      <th>weekday_6</th>\n",
       "      <th>weekday_7</th>\n",
       "      <th>label</th>\n",
       "      <th>pred_prob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>...</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.019557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.101050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160516</td>\n",
       "      <td>20160613</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.101050</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2029232</td>\n",
       "      <td>450</td>\n",
       "      <td>1532</td>\n",
       "      <td>30:5</td>\n",
       "      <td>0</td>\n",
       "      <td>20160530</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.096889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2029232</td>\n",
       "      <td>6459</td>\n",
       "      <td>12737</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160519</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.132660</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "    User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "1   1439408         4663     11002        150:20        1      20160528   \n",
       "4   1439408         2632      8591          20:1        0      20160613   \n",
       "6   1439408         2632      8591          20:1        0      20160516   \n",
       "9   2029232          450      1532          30:5        0      20160530   \n",
       "10  2029232         6459     12737          20:1        0      20160519   \n",
       "\n",
       "        Date discount_type  discount_rate  discount_man    ...      \\\n",
       "1       null             1       0.866667           150    ...       \n",
       "4       null             1       0.950000            20    ...       \n",
       "6   20160613             1       0.950000            20    ...       \n",
       "9       null             1       0.833333            30    ...       \n",
       "10      null             1       0.950000            20    ...       \n",
       "\n",
       "    weekday_type  weekday_1 weekday_2  weekday_3  weekday_4  weekday_5  \\\n",
       "1              1          0         0          0          0          0   \n",
       "4              0          1         0          0          0          0   \n",
       "6              0          1         0          0          0          0   \n",
       "9              0          1         0          0          0          0   \n",
       "10             0          0         0          0          1          0   \n",
       "\n",
       "    weekday_6  weekday_7  label  pred_prob  \n",
       "1           1          0      0   0.019557  \n",
       "4           0          0      0   0.101050  \n",
       "6           0          0      0   0.101050  \n",
       "9           0          0      0   0.096889  \n",
       "10          0          0      0   0.132660  \n",
       "\n",
       "[5 rows x 23 columns]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# valid predict\n",
    "y_valid_pred = model.predict_proba(valid[predictors])\n",
    "valid1 = valid.copy()\n",
    "valid1['pred_prob'] = y_valid_pred[:, 1]\n",
    "valid1.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计算 AUC："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.532344469452\n"
     ]
    }
   ],
   "source": [
    "# avgAUC calculation\n",
    "vg = valid1.groupby(['Coupon_id'])\n",
    "aucs = []\n",
    "for i in vg:\n",
    "    tmpdf = i[1] \n",
    "    if len(tmpdf['label'].unique()) != 2:\n",
    "        continue\n",
    "    fpr, tpr, thresholds = roc_curve(tmpdf['label'], tmpdf['pred_prob'], pos_label=1)\n",
    "    aucs.append(auc(fpr, tpr))\n",
    "print(np.average(aucs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 测试"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Probability</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4129537</td>\n",
       "      <td>9983</td>\n",
       "      <td>20160712</td>\n",
       "      <td>0.105114</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6949378</td>\n",
       "      <td>3429</td>\n",
       "      <td>20160706</td>\n",
       "      <td>0.153999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2166529</td>\n",
       "      <td>6928</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0.005537</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2166529</td>\n",
       "      <td>1808</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0.018767</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6172162</td>\n",
       "      <td>6500</td>\n",
       "      <td>20160708</td>\n",
       "      <td>0.063588</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Coupon_id  Date_received  Probability\n",
       "0  4129537       9983       20160712     0.105114\n",
       "1  6949378       3429       20160706     0.153999\n",
       "2  2166529       6928       20160727     0.005537\n",
       "3  2166529       1808       20160727     0.018767\n",
       "4  6172162       6500       20160708     0.063588"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test prediction for submission\n",
    "y_test_pred = model.predict_proba(dftest[predictors])\n",
    "dftest1 = dftest[['User_id','Coupon_id','Date_received']].copy()\n",
    "dftest1['Probability'] = y_test_pred[:,1]\n",
    "dftest1.to_csv('submit1.csv', index=False, header=False)\n",
    "dftest1.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存模型 & 导入模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if not os.path.isfile('1_model.pkl'):\n",
    "    with open('1_model.pkl', 'wb') as f:\n",
    "        pickle.dump(model, f)\n",
    "else:\n",
    "    with open('1_model.pkl', 'rb') as f:\n",
    "        model = pickle.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 优化模型"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 特征提取"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "通过客户和商户以前的买卖情况，提取各自或者交叉的特征。这里使用20160101到20160515之间的数据提取特征，20160516-20160615的数据作为训练集（划分训练集和验证集）。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    229715\n",
      "1     22871\n",
      "Name: label, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "feature = dfoff[(dfoff['Date'] < '20160516') | ((dfoff['Date'] == 'null') & (dfoff['Date_received'] < '20160516'))].copy()\n",
    "data = dfoff[(dfoff['Date_received'] >= '20160516') & (dfoff['Date_received'] <= '20160615')].copy()\n",
    "print(data['label'].value_counts())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "fdf = feature.copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 用户 User 特征"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u：用户统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# key of user\n",
    "u = fdf[['User_id']].copy().drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u1:用户接收到优惠券的数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_coupon_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>64</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>110</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_coupon_count\n",
       "0        4               1\n",
       "1       35               4\n",
       "2       36               2\n",
       "3       64               1\n",
       "4      110               3"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_coupon_count : num of coupon received by user\n",
    "u1 = fdf[fdf['Date_received'] != 'null'][['User_id']].copy()\n",
    "u1['u_coupon_count'] = 1\n",
    "u1 = u1.groupby(['User_id'], as_index = False).count()\n",
    "u1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u2：用户购买的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_buy_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>165</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>184</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>215</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_buy_count\n",
       "0      165           10\n",
       "1      184            1\n",
       "2      215            1\n",
       "3      285            1\n",
       "4      315            3"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_buy_count : times of user buy offline (with or without coupon)\n",
    "u2 = fdf[fdf['Date'] != 'null'][['User_id']].copy()\n",
    "u2['u_buy_count'] = 1\n",
    "u2 = u2.groupby(['User_id'], as_index = False).count()\n",
    "u2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u3：用户使用优惠券购买的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_buy_with_coupon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>184</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>417</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>687</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>696</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>947</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_buy_with_coupon\n",
       "0      184                  1\n",
       "1      417                  1\n",
       "2      687                  2\n",
       "3      696                  1\n",
       "4      947                  1"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_buy_with_coupon : times of user buy offline (with coupon)\n",
    "u3 = fdf[((fdf['Date'] != 'null') & (fdf['Date_received'] != 'null'))][['User_id']].copy()\n",
    "u3['u_buy_with_coupon'] = 1\n",
    "u3 = u3.groupby(['User_id'], as_index = False).count()\n",
    "u3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u4：用户购买的商家个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_merchant_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>165</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>184</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>215</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>285</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>315</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_merchant_count\n",
       "0      165                 2\n",
       "1      184                 1\n",
       "2      215                 1\n",
       "3      285                 1\n",
       "4      315                 2"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_merchant_count : num of merchant user bought from\n",
    "u4 = fdf[fdf['Date'] != 'null'][['User_id', 'Merchant_id']].copy()\n",
    "u4.drop_duplicates(inplace = True)\n",
    "u4 = u4.groupby(['User_id'], as_index = False).count()\n",
    "u4.rename(columns = {'Merchant_id':'u_merchant_count'}, inplace = True)\n",
    "u4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u5：用户使用优惠券购买商品距离商店的最小距离\n",
    "\n",
    "u6：用户使用优惠券购买商品距离商店的最大距离\n",
    "\n",
    "u7：用户使用优惠券购买商品距离商店的平均距离\n",
    "\n",
    "u8：用户使用优惠券购买商品距离商店的中位数距离"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_median_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>184</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>417</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>687</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>696</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>947</td>\n",
       "      <td>8.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_median_distance\n",
       "0      184                0.0\n",
       "1      417                0.0\n",
       "2      687                NaN\n",
       "3      696                0.0\n",
       "4      947                8.0"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_min_distance\n",
    "utmp = fdf[(fdf['Date'] != 'null') & (fdf['Date_received'] != 'null')][['User_id', 'distance']].copy()\n",
    "utmp.replace(-1, np.nan, inplace = True)\n",
    "u5 = utmp.groupby(['User_id'], as_index = False).min()\n",
    "u5.rename(columns = {'distance':'u_min_distance'}, inplace = True)\n",
    "u6 = utmp.groupby(['User_id'], as_index = False).max()\n",
    "u6.rename(columns = {'distance':'u_max_distance'}, inplace = True)\n",
    "u7 = utmp.groupby(['User_id'], as_index = False).mean()\n",
    "u7.rename(columns = {'distance':'u_mean_distance'}, inplace = True)\n",
    "u8 = utmp.groupby(['User_id'], as_index = False).median()\n",
    "u8.rename(columns = {'distance':'u_median_distance'}, inplace = True)\n",
    "u8.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据 User_id，将 u1,u2,u3,u4,u5,u6,u7,u8 整合成 user_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge all the features on key User_id\n",
    "user_feature = pd.merge(u, u1, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u2, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u3, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u4, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u5, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u6, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u7, on = 'User_id', how = 'left')\n",
    "user_feature = pd.merge(user_feature, u8, on = 'User_id', how = 'left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "u_use_coupon_rate：对于用户来说，接收到的优惠券使用率\n",
    "\n",
    "u_buy_with_coupon_rate：用户所有购买行为中使用优惠券占的比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>u_coupon_count</th>\n",
       "      <th>u_buy_count</th>\n",
       "      <th>u_buy_with_coupon</th>\n",
       "      <th>u_merchant_count</th>\n",
       "      <th>u_min_distance</th>\n",
       "      <th>u_max_distance</th>\n",
       "      <th>u_mean_distance</th>\n",
       "      <th>u_median_distance</th>\n",
       "      <th>u_use_coupon_rate</th>\n",
       "      <th>u_buy_with_coupon_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1832624</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2029232</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2223968</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73611</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  u_coupon_count  u_buy_count  u_buy_with_coupon  u_merchant_count  \\\n",
       "0  1439408             2.0          1.0                0.0               1.0   \n",
       "1  1832624             1.0          0.0                0.0               0.0   \n",
       "2  2029232             1.0          0.0                0.0               0.0   \n",
       "3  2223968             1.0          0.0                0.0               0.0   \n",
       "4    73611             1.0          0.0                0.0               0.0   \n",
       "\n",
       "   u_min_distance  u_max_distance  u_mean_distance  u_median_distance  \\\n",
       "0             0.0             0.0              0.0                0.0   \n",
       "1             0.0             0.0              0.0                0.0   \n",
       "2             0.0             0.0              0.0                0.0   \n",
       "3             0.0             0.0              0.0                0.0   \n",
       "4             0.0             0.0              0.0                0.0   \n",
       "\n",
       "   u_use_coupon_rate  u_buy_with_coupon_rate  \n",
       "0                0.0                     0.0  \n",
       "1                0.0                     0.0  \n",
       "2                0.0                     0.0  \n",
       "3                0.0                     0.0  \n",
       "4                0.0                     0.0  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# calculate rate\n",
    "user_feature['u_use_coupon_rate'] = user_feature['u_buy_with_coupon'].astype('float')/user_feature['u_coupon_count'].astype('float')\n",
    "user_feature['u_buy_with_coupon_rate'] = user_feature['u_buy_with_coupon'].astype('float')/user_feature['u_buy_count'].astype('float')\n",
    "user_feature = user_feature.fillna(0)\n",
    "user_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 商户 Merchant 特征"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m：商户统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# key of merchant\n",
    "m = fdf[['Merchant_id']].copy().drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m1：每个商户发放的优惠券数量"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_coupon_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>13</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_coupon_count\n",
       "0            2               1\n",
       "1            5               5\n",
       "2            8               2\n",
       "3           13               3\n",
       "4           14              10"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# m_coupon_count : num of coupon from merchant\n",
    "m1 = fdf[fdf['Date_received'] != 'null'][['Merchant_id']].copy()\n",
    "m1['m_coupon_count'] = 1\n",
    "m1 = m1.groupby(['Merchant_id'], as_index = False).count()\n",
    "m1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m2：商户销售的次数（不考虑是否使用优惠券）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_sale_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_sale_count\n",
       "0            1             5\n",
       "1            3             8\n",
       "2            4            19\n",
       "3            5            27\n",
       "4            6            42"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# m_sale_count : num of sale from merchant (with or without coupon)\n",
    "m2 = fdf[fdf['Date'] != 'null'][['Merchant_id']].copy()\n",
    "m2['m_sale_count'] = 1\n",
    "m2 = m2.groupby(['Merchant_id'], as_index = False).count()\n",
    "m2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m3：商户使用优惠券销售的次数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_sale_with_coupon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_sale_with_coupon\n",
       "0           13                   1\n",
       "1           14                   1\n",
       "2           15                  11\n",
       "3           17                   3\n",
       "4           18                   2"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# m_sale_with_coupon : num of sale from merchant with coupon usage\n",
    "m3 = fdf[(fdf['Date'] != 'null') & (fdf['Date_received'] != 'null')][['Merchant_id']].copy()\n",
    "m3['m_sale_with_coupon'] = 1\n",
    "m3 = m3.groupby(['Merchant_id'], as_index = False).count()\n",
    "m3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m4：商家销售的用户个数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_user_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>5</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_user_count\n",
       "0            1             2\n",
       "1            3             8\n",
       "2            4             2\n",
       "3            5             9\n",
       "4            6             8"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# u_merchant_count : num of merchant user bought from\n",
    "m4 = fdf[fdf['Date'] != 'null'][['User_id', 'Merchant_id']].copy()\n",
    "m4.drop_duplicates(inplace = True)\n",
    "m4 = m4.groupby(['Merchant_id'], as_index = False).count()\n",
    "m4.rename(columns = {'User_id':'m_user_count'}, inplace = True)\n",
    "m4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m5：商户使用优惠券销售商品距离用户的最小距离\n",
    "\n",
    "m6：商户使用优惠券销售商品距离用户的最大距离\n",
    "\n",
    "m7：商户使用优惠券销售商品距离用户的平均距离\n",
    "\n",
    "m8：商户使用优惠券销售商品距离用户的中位数距离"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_median_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>13</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>15</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>17</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>18</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_median_distance\n",
       "0           13                0.0\n",
       "1           14                0.0\n",
       "2           15                0.0\n",
       "3           17                0.0\n",
       "4           18                0.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# m_min_distance\n",
    "mtmp = fdf[(fdf['Date'] != 'null') & (fdf['Date_received'] != 'null')][['Merchant_id', 'distance']].copy()\n",
    "mtmp.replace(-1, np.nan, inplace = True)\n",
    "m5 = mtmp.groupby(['Merchant_id'], as_index = False).min()\n",
    "m5.rename(columns = {'distance':'m_min_distance'}, inplace = True)\n",
    "m6 = mtmp.groupby(['Merchant_id'], as_index = False).max()\n",
    "m6.rename(columns = {'distance':'m_max_distance'}, inplace = True)\n",
    "m7 = mtmp.groupby(['Merchant_id'], as_index = False).mean()\n",
    "m7.rename(columns = {'distance':'m_mean_distance'}, inplace = True)\n",
    "m8 = mtmp.groupby(['Merchant_id'], as_index = False).median()\n",
    "m8.rename(columns = {'distance':'m_median_distance'}, inplace = True)\n",
    "m8.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据 Merchant_id，将 m1,m2,m3,m4,m5,m6,m7,m8 整合成 merchant_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_coupon_count</th>\n",
       "      <th>m_sale_count</th>\n",
       "      <th>m_sale_with_coupon</th>\n",
       "      <th>m_user_count</th>\n",
       "      <th>m_min_distance</th>\n",
       "      <th>m_max_distance</th>\n",
       "      <th>m_mean_distance</th>\n",
       "      <th>m_median_distance</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2632</td>\n",
       "      <td>35.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3381</td>\n",
       "      <td>122834.0</td>\n",
       "      <td>18080.0</td>\n",
       "      <td>2487.0</td>\n",
       "      <td>11006.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.652429</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2099</td>\n",
       "      <td>16824.0</td>\n",
       "      <td>7227.0</td>\n",
       "      <td>1705.0</td>\n",
       "      <td>2751.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.968072</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1569</td>\n",
       "      <td>33492.0</td>\n",
       "      <td>896.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>683.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.260163</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4833</td>\n",
       "      <td>8321.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.037736</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_coupon_count  m_sale_count  m_sale_with_coupon  \\\n",
       "0         2632            35.0          18.0                 3.0   \n",
       "1         3381        122834.0       18080.0              2487.0   \n",
       "2         2099         16824.0        7227.0              1705.0   \n",
       "3         1569         33492.0         896.0               135.0   \n",
       "4         4833          8321.0         636.0               116.0   \n",
       "\n",
       "   m_user_count  m_min_distance  m_max_distance  m_mean_distance  \\\n",
       "0           8.0             1.0             1.0         1.000000   \n",
       "1       11006.0             0.0            10.0         1.652429   \n",
       "2        2751.0             0.0            10.0         0.968072   \n",
       "3         683.0             0.0            10.0         2.260163   \n",
       "4         270.0             0.0            10.0         3.037736   \n",
       "\n",
       "   m_median_distance  \n",
       "0                1.0  \n",
       "1                1.0  \n",
       "2                0.0  \n",
       "3                1.0  \n",
       "4                2.0  "
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchant_feature = pd.merge(m, m1, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m2, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m3, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m4, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m5, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m6, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m7, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = pd.merge(merchant_feature, m8, on = 'Merchant_id', how = 'left')\n",
    "merchant_feature = merchant_feature.fillna(0)\n",
    "merchant_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "m_coupon_use_rate： 对于商家来说，发放的优惠卷使用率\n",
    "\n",
    "m_sale_with_coupon_rate：商家所有销售行为中使用优惠卷占的比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>m_coupon_count</th>\n",
       "      <th>m_sale_count</th>\n",
       "      <th>m_sale_with_coupon</th>\n",
       "      <th>m_user_count</th>\n",
       "      <th>m_min_distance</th>\n",
       "      <th>m_max_distance</th>\n",
       "      <th>m_mean_distance</th>\n",
       "      <th>m_median_distance</th>\n",
       "      <th>m_coupon_use_rate</th>\n",
       "      <th>m_sale_with_coupon_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2632</td>\n",
       "      <td>35.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.166667</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3381</td>\n",
       "      <td>122834.0</td>\n",
       "      <td>18080.0</td>\n",
       "      <td>2487.0</td>\n",
       "      <td>11006.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>1.652429</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.020247</td>\n",
       "      <td>0.137555</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2099</td>\n",
       "      <td>16824.0</td>\n",
       "      <td>7227.0</td>\n",
       "      <td>1705.0</td>\n",
       "      <td>2751.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>0.968072</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.101343</td>\n",
       "      <td>0.235921</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1569</td>\n",
       "      <td>33492.0</td>\n",
       "      <td>896.0</td>\n",
       "      <td>135.0</td>\n",
       "      <td>683.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>2.260163</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.004031</td>\n",
       "      <td>0.150670</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4833</td>\n",
       "      <td>8321.0</td>\n",
       "      <td>636.0</td>\n",
       "      <td>116.0</td>\n",
       "      <td>270.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>10.0</td>\n",
       "      <td>3.037736</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.013941</td>\n",
       "      <td>0.182390</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Merchant_id  m_coupon_count  m_sale_count  m_sale_with_coupon  \\\n",
       "0         2632            35.0          18.0                 3.0   \n",
       "1         3381        122834.0       18080.0              2487.0   \n",
       "2         2099         16824.0        7227.0              1705.0   \n",
       "3         1569         33492.0         896.0               135.0   \n",
       "4         4833          8321.0         636.0               116.0   \n",
       "\n",
       "   m_user_count  m_min_distance  m_max_distance  m_mean_distance  \\\n",
       "0           8.0             1.0             1.0         1.000000   \n",
       "1       11006.0             0.0            10.0         1.652429   \n",
       "2        2751.0             0.0            10.0         0.968072   \n",
       "3         683.0             0.0            10.0         2.260163   \n",
       "4         270.0             0.0            10.0         3.037736   \n",
       "\n",
       "   m_median_distance  m_coupon_use_rate  m_sale_with_coupon_rate  \n",
       "0                1.0           0.085714                 0.166667  \n",
       "1                1.0           0.020247                 0.137555  \n",
       "2                0.0           0.101343                 0.235921  \n",
       "3                1.0           0.004031                 0.150670  \n",
       "4                2.0           0.013941                 0.182390  "
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merchant_feature['m_coupon_use_rate'] = merchant_feature['m_sale_with_coupon'].astype('float')/merchant_feature['m_coupon_count'].astype('float')\n",
    "merchant_feature['m_sale_with_coupon_rate'] = merchant_feature['m_sale_with_coupon'].astype('float')/merchant_feature['m_sale_count'].astype('float')\n",
    "merchant_feature = merchant_feature.fillna(0)\n",
    "merchant_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 用户和商户 User & Merchant 交叉特征"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "um1：用户和商户对统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# key of user and merchant\n",
    "um = fdf[['User_id', 'Merchant_id']].copy().drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>um_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1433</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>3381</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>1041</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>36</td>\n",
       "      <td>5717</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>64</td>\n",
       "      <td>2146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  um_count\n",
       "0        4         1433         1\n",
       "1       35         3381         4\n",
       "2       36         1041         1\n",
       "3       36         5717         1\n",
       "4       64         2146         1"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "um1 = fdf[['User_id', 'Merchant_id']].copy()\n",
    "um1['um_count'] = 1\n",
    "um1 = um1.groupby(['User_id', 'Merchant_id'], as_index = False).count()\n",
    "um1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "um2：每个用户商户对交易统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>um_buy_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>165</td>\n",
       "      <td>2934</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>165</td>\n",
       "      <td>4195</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>184</td>\n",
       "      <td>3381</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>215</td>\n",
       "      <td>129</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>285</td>\n",
       "      <td>450</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  um_buy_count\n",
       "0      165         2934             6\n",
       "1      165         4195             4\n",
       "2      184         3381             1\n",
       "3      215          129             1\n",
       "4      285          450             1"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "um2 = fdf[fdf['Date'] != 'null'][['User_id', 'Merchant_id']].copy()\n",
    "um2['um_buy_count'] = 1\n",
    "um2 = um2.groupby(['User_id', 'Merchant_id'], as_index = False).count()\n",
    "um2.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "um3：每个用户商户对发放优惠券的统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>um_coupon_count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4</td>\n",
       "      <td>1433</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>35</td>\n",
       "      <td>3381</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>36</td>\n",
       "      <td>1041</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>36</td>\n",
       "      <td>5717</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>64</td>\n",
       "      <td>2146</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  um_coupon_count\n",
       "0        4         1433                1\n",
       "1       35         3381                4\n",
       "2       36         1041                1\n",
       "3       36         5717                1\n",
       "4       64         2146                1"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "um3 = fdf[fdf['Date_received'] != 'null'][['User_id', 'Merchant_id']].copy()\n",
    "um3['um_coupon_count'] = 1\n",
    "um3 = um3.groupby(['User_id', 'Merchant_id'], as_index = False).count()\n",
    "um3.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "um4：每个用户商户对使用优惠卷的交易行为的统计"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>184</td>\n",
       "      <td>3381</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>417</td>\n",
       "      <td>775</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>687</td>\n",
       "      <td>6454</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>687</td>\n",
       "      <td>8594</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>696</td>\n",
       "      <td>4195</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  um_buy_with_coupon\n",
       "0      184         3381                   1\n",
       "1      417          775                   1\n",
       "2      687         6454                   1\n",
       "3      687         8594                   1\n",
       "4      696         4195                   1"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "um4 = fdf[(fdf['Date_received'] != 'null') & (fdf['Date'] != 'null')][['User_id', 'Merchant_id']].copy()\n",
    "um4['um_buy_with_coupon'] = 1\n",
    "um4 = um4.groupby(['User_id', 'Merchant_id'], as_index = False).count()\n",
    "um4.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据 User_id 和 Merchant_id，将 um1,um2,um3,um4 整合成 user_merchant_feature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge all user merchant \n",
    "user_merchant_feature = pd.merge(um, um1, on = ['User_id','Merchant_id'], how = 'left')\n",
    "user_merchant_feature = pd.merge(user_merchant_feature, um2, on = ['User_id','Merchant_id'], how = 'left')\n",
    "user_merchant_feature = pd.merge(user_merchant_feature, um3, on = ['User_id','Merchant_id'], how = 'left')\n",
    "user_merchant_feature = pd.merge(user_merchant_feature, um4, on = ['User_id','Merchant_id'], how = 'left')\n",
    "user_merchant_feature = user_merchant_feature.fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "um_buy_rate：每个用户商户对交易行为占所有用户商户对的比例\n",
    "\n",
    "um_coupon_use_rate：使用优惠卷的交易行为占所有用户商户对发放优惠卷的比例\n",
    "\n",
    "um_buy_with_coupon_rate：使用优惠卷的交易行为占所有用户商户对交易行为的比例"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>um_count</th>\n",
       "      <th>um_buy_count</th>\n",
       "      <th>um_coupon_count</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "      <th>um_buy_rate</th>\n",
       "      <th>um_coupon_use_rate</th>\n",
       "      <th>um_buy_with_coupon_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1832624</td>\n",
       "      <td>3381</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2029232</td>\n",
       "      <td>3381</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2223968</td>\n",
       "      <td>3381</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>73611</td>\n",
       "      <td>2099</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  um_count  um_buy_count  um_coupon_count  \\\n",
       "0  1439408         2632         3           1.0              2.0   \n",
       "1  1832624         3381         1           0.0              1.0   \n",
       "2  2029232         3381         1           0.0              1.0   \n",
       "3  2223968         3381         1           0.0              1.0   \n",
       "4    73611         2099         1           0.0              1.0   \n",
       "\n",
       "   um_buy_with_coupon  um_buy_rate  um_coupon_use_rate  \\\n",
       "0                 0.0     0.333333                 0.0   \n",
       "1                 0.0     0.000000                 0.0   \n",
       "2                 0.0     0.000000                 0.0   \n",
       "3                 0.0     0.000000                 0.0   \n",
       "4                 0.0     0.000000                 0.0   \n",
       "\n",
       "   um_buy_with_coupon_rate  \n",
       "0                      0.0  \n",
       "1                      0.0  \n",
       "2                      0.0  \n",
       "3                      0.0  \n",
       "4                      0.0  "
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "user_merchant_feature['um_buy_rate'] = user_merchant_feature['um_buy_count'].astype('float')/user_merchant_feature['um_count'].astype('float')\n",
    "user_merchant_feature['um_coupon_use_rate'] = user_merchant_feature['um_buy_with_coupon'].astype('float')/user_merchant_feature['um_coupon_count'].astype('float')\n",
    "user_merchant_feature['um_buy_with_coupon_rate'] = user_merchant_feature['um_buy_with_coupon'].astype('float')/user_merchant_feature['um_buy_count'].astype('float')\n",
    "user_merchant_feature = user_merchant_feature.fillna(0)\n",
    "user_merchant_feature.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 将 user_feature, merchant_feature, user_merchant_feature 放入训练集和测试集中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# add user_feature, merchant_feature, user_merchant_feature to train data \n",
    "data2 = pd.merge(data, user_feature, on = 'User_id', how = 'left').fillna(0)\n",
    "data3 = pd.merge(data2, merchant_feature, on = 'Merchant_id', how = 'left').fillna(0)\n",
    "data4 = pd.merge(data3, user_merchant_feature, on = ['User_id','Merchant_id'], how = 'left').fillna(0)\n",
    "train = data4.copy()\n",
    "\n",
    "# add user_feature, merchant_feature, user_merchant_feature to test data \n",
    "data2 = pd.merge(dftest, user_feature, on = 'User_id', how = 'left').fillna(0)\n",
    "data3 = pd.merge(data2, merchant_feature, on = 'Merchant_id', how = 'left').fillna(0)\n",
    "data4 = pd.merge(data3, user_merchant_feature, on = ['User_id','Merchant_id'], how = 'left').fillna(0)\n",
    "test = data4.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>m_median_distance</th>\n",
       "      <th>m_coupon_use_rate</th>\n",
       "      <th>m_sale_with_coupon_rate</th>\n",
       "      <th>um_count</th>\n",
       "      <th>um_buy_count</th>\n",
       "      <th>um_coupon_count</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "      <th>um_buy_rate</th>\n",
       "      <th>um_coupon_use_rate</th>\n",
       "      <th>um_buy_with_coupon_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1439408</td>\n",
       "      <td>4663</td>\n",
       "      <td>11002</td>\n",
       "      <td>150:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160528</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.866667</td>\n",
       "      <td>150</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.010323</td>\n",
       "      <td>0.034722</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160613</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1439408</td>\n",
       "      <td>2632</td>\n",
       "      <td>8591</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160516</td>\n",
       "      <td>20160613</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.085714</td>\n",
       "      <td>0.166667</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.333333</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2029232</td>\n",
       "      <td>450</td>\n",
       "      <td>1532</td>\n",
       "      <td>30:5</td>\n",
       "      <td>0</td>\n",
       "      <td>20160530</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.015813</td>\n",
       "      <td>0.079193</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2029232</td>\n",
       "      <td>6459</td>\n",
       "      <td>12737</td>\n",
       "      <td>20:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160519</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.950000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 49 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "0  1439408         4663     11002        150:20        1      20160528   \n",
       "1  1439408         2632      8591          20:1        0      20160613   \n",
       "2  1439408         2632      8591          20:1        0      20160516   \n",
       "3  2029232          450      1532          30:5        0      20160530   \n",
       "4  2029232         6459     12737          20:1        0      20160519   \n",
       "\n",
       "       Date  discount_type  discount_rate  discount_man  \\\n",
       "0      null              1       0.866667           150   \n",
       "1      null              1       0.950000            20   \n",
       "2  20160613              1       0.950000            20   \n",
       "3      null              1       0.833333            30   \n",
       "4      null              1       0.950000            20   \n",
       "\n",
       "            ...             m_median_distance  m_coupon_use_rate  \\\n",
       "0           ...                           0.0           0.010323   \n",
       "1           ...                           1.0           0.085714   \n",
       "2           ...                           1.0           0.085714   \n",
       "3           ...                           0.0           0.015813   \n",
       "4           ...                           0.0           0.000000   \n",
       "\n",
       "   m_sale_with_coupon_rate  um_count  um_buy_count  um_coupon_count  \\\n",
       "0                 0.034722       0.0           0.0              0.0   \n",
       "1                 0.166667       3.0           1.0              2.0   \n",
       "2                 0.166667       3.0           1.0              2.0   \n",
       "3                 0.079193       0.0           0.0              0.0   \n",
       "4                 0.000000       0.0           0.0              0.0   \n",
       "\n",
       "   um_buy_with_coupon  um_buy_rate  um_coupon_use_rate  \\\n",
       "0                 0.0     0.000000                 0.0   \n",
       "1                 0.0     0.333333                 0.0   \n",
       "2                 0.0     0.333333                 0.0   \n",
       "3                 0.0     0.000000                 0.0   \n",
       "4                 0.0     0.000000                 0.0   \n",
       "\n",
       "   um_buy_with_coupon_rate  \n",
       "0                      0.0  \n",
       "1                      0.0  \n",
       "2                      0.0  \n",
       "3                      0.0  \n",
       "4                      0.0  \n",
       "\n",
       "[5 rows x 49 columns]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "train.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>discount_jian</th>\n",
       "      <th>...</th>\n",
       "      <th>m_median_distance</th>\n",
       "      <th>m_coupon_use_rate</th>\n",
       "      <th>m_sale_with_coupon_rate</th>\n",
       "      <th>um_count</th>\n",
       "      <th>um_buy_count</th>\n",
       "      <th>um_coupon_count</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "      <th>um_buy_rate</th>\n",
       "      <th>um_coupon_use_rate</th>\n",
       "      <th>um_buy_with_coupon_rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4129537</td>\n",
       "      <td>450</td>\n",
       "      <td>9983</td>\n",
       "      <td>30:5</td>\n",
       "      <td>1</td>\n",
       "      <td>20160712</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.015813</td>\n",
       "      <td>0.079193</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6949378</td>\n",
       "      <td>1300</td>\n",
       "      <td>3429</td>\n",
       "      <td>30:5</td>\n",
       "      <td>null</td>\n",
       "      <td>20160706</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>6928</td>\n",
       "      <td>200:20</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "      <td>1</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>200</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.016321</td>\n",
       "      <td>0.022189</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2166529</td>\n",
       "      <td>7113</td>\n",
       "      <td>1808</td>\n",
       "      <td>100:10</td>\n",
       "      <td>5</td>\n",
       "      <td>20160727</td>\n",
       "      <td>1</td>\n",
       "      <td>0.900000</td>\n",
       "      <td>100</td>\n",
       "      <td>10</td>\n",
       "      <td>...</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.016321</td>\n",
       "      <td>0.022189</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6172162</td>\n",
       "      <td>7605</td>\n",
       "      <td>6500</td>\n",
       "      <td>30:1</td>\n",
       "      <td>2</td>\n",
       "      <td>20160708</td>\n",
       "      <td>1</td>\n",
       "      <td>0.966667</td>\n",
       "      <td>30</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.203196</td>\n",
       "      <td>0.119143</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 47 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Merchant_id  Coupon_id Discount_rate Distance  Date_received  \\\n",
       "0  4129537          450       9983          30:5        1       20160712   \n",
       "1  6949378         1300       3429          30:5     null       20160706   \n",
       "2  2166529         7113       6928        200:20        5       20160727   \n",
       "3  2166529         7113       1808        100:10        5       20160727   \n",
       "4  6172162         7605       6500          30:1        2       20160708   \n",
       "\n",
       "   discount_type  discount_rate  discount_man  discount_jian  \\\n",
       "0              1       0.833333            30              5   \n",
       "1              1       0.833333            30              5   \n",
       "2              1       0.900000           200             20   \n",
       "3              1       0.900000           100             10   \n",
       "4              1       0.966667            30              1   \n",
       "\n",
       "            ...             m_median_distance  m_coupon_use_rate  \\\n",
       "0           ...                           0.0           0.015813   \n",
       "1           ...                           0.0           0.000000   \n",
       "2           ...                           1.0           0.016321   \n",
       "3           ...                           1.0           0.016321   \n",
       "4           ...                           0.0           0.203196   \n",
       "\n",
       "   m_sale_with_coupon_rate  um_count  um_buy_count  um_coupon_count  \\\n",
       "0                 0.079193       0.0           0.0              0.0   \n",
       "1                 0.000000       0.0           0.0              0.0   \n",
       "2                 0.022189       0.0           0.0              0.0   \n",
       "3                 0.022189       0.0           0.0              0.0   \n",
       "4                 0.119143       0.0           0.0              0.0   \n",
       "\n",
       "   um_buy_with_coupon  um_buy_rate  um_coupon_use_rate  \\\n",
       "0                 0.0          0.0                 0.0   \n",
       "1                 0.0          0.0                 0.0   \n",
       "2                 0.0          0.0                 0.0   \n",
       "3                 0.0          0.0                 0.0   \n",
       "4                 0.0          0.0                 0.0   \n",
       "\n",
       "   um_buy_with_coupon_rate  \n",
       "0                      0.0  \n",
       "1                      0.0  \n",
       "2                      0.0  \n",
       "3                      0.0  \n",
       "4                      0.0  \n",
       "\n",
       "[5 rows x 47 columns]"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 所有特征"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "41 ['discount_rate', 'discount_type', 'discount_man', 'discount_jian', 'distance', 'weekday', 'weekday_type', 'weekday_1', 'weekday_2', 'weekday_3', 'weekday_4', 'weekday_5', 'weekday_6', 'weekday_7', 'u_coupon_count', 'u_buy_count', 'u_buy_with_coupon', 'u_merchant_count', 'u_min_distance', 'u_max_distance', 'u_mean_distance', 'u_median_distance', 'u_use_coupon_rate', 'u_buy_with_coupon_rate', 'm_coupon_count', 'm_sale_count', 'm_sale_with_coupon', 'm_user_count', 'm_min_distance', 'm_max_distance', 'm_mean_distance', 'm_median_distance', 'm_coupon_use_rate', 'm_sale_with_coupon_rate', 'um_count', 'um_buy_count', 'um_coupon_count', 'um_buy_with_coupon', 'um_buy_rate', 'um_coupon_use_rate', 'um_buy_with_coupon_rate']\n"
     ]
    }
   ],
   "source": [
    "predictors = original_feature + user_feature.columns.tolist()[1:] + \\\n",
    "             merchant_feature.columns.tolist()[1:] + \\\n",
    "             user_merchant_feature.columns.tolist()[2:]\n",
    "print(len(predictors),predictors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 划分训练集和验证集"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "trainSub, validSub = train_test_split(train, test_size = 0.2, stratify = train['label'], random_state=100)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 线性模型 SGDClassifier"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 建立模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def check_model(data, predictors):\n",
    "    \n",
    "    classifier = lambda: SGDClassifier(\n",
    "        loss='log',  # loss function: logistic regression\n",
    "        penalty='elasticnet', # L1 & L2\n",
    "        fit_intercept=True,  # 是否存在截距，默认存在\n",
    "        max_iter=100, \n",
    "        shuffle=True,  # Whether or not the training data should be shuffled after each epoch\n",
    "        n_jobs=1, # The number of processors to use\n",
    "        class_weight=None) # Weights associated with classes. If not given, all classes are supposed to have weight one.\n",
    " \n",
    "    # 管道机制使得参数集在新数据集（比如测试集）上的重复使用，管道机制实现了对全部步骤的流式化封装和管理。\n",
    "    model = Pipeline(steps=[\n",
    "        ('ss', StandardScaler()), # transformer\n",
    "        ('en', classifier())  # estimator\n",
    "    ])\n",
    " \n",
    "    parameters = {\n",
    "        'en__alpha': [ 0.001, 0.01, 0.1],\n",
    "        'en__l1_ratio': [ 0.001, 0.01, 0.1]\n",
    "    }\n",
    " \n",
    "    # StratifiedKFold用法类似Kfold，但是他是分层采样，确保训练集，测试集中各类别样本的比例与原始数据集中相同。\n",
    "    folder = StratifiedKFold(n_splits=3, shuffle=True)\n",
    "    \n",
    "    # Exhaustive search over specified parameter values for an estimator.\n",
    "    grid_search = GridSearchCV(\n",
    "        model, \n",
    "        parameters, \n",
    "        cv=folder, \n",
    "        n_jobs=-1,  # -1 means using all processors\n",
    "        verbose=1)\n",
    "    grid_search = grid_search.fit(data[predictors], \n",
    "                                  data['label'])\n",
    "    \n",
    "    return grid_search"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Fitting 3 folds for each of 9 candidates, totalling 27 fits\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "[Parallel(n_jobs=-1)]: Done  27 out of  27 | elapsed:   56.3s finished\n"
     ]
    }
   ],
   "source": [
    "model = check_model(trainSub, predictors)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 验证"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对验证集中每个优惠券预测的结果计算 AUC，再对所有优惠券的 AUC 求平均。计算 AUC 的时候，如果 label 只有一类，就直接跳过，因为 AUC 无法计算。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>m_coupon_use_rate</th>\n",
       "      <th>m_sale_with_coupon_rate</th>\n",
       "      <th>um_count</th>\n",
       "      <th>um_buy_count</th>\n",
       "      <th>um_coupon_count</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "      <th>um_buy_rate</th>\n",
       "      <th>um_coupon_use_rate</th>\n",
       "      <th>um_buy_with_coupon_rate</th>\n",
       "      <th>pred_prob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166704</th>\n",
       "      <td>2266960</td>\n",
       "      <td>5717</td>\n",
       "      <td>8192</td>\n",
       "      <td>20:5</td>\n",
       "      <td>1</td>\n",
       "      <td>20160525</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.021105</td>\n",
       "      <td>0.052307</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.025477</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183019</th>\n",
       "      <td>1748229</td>\n",
       "      <td>4579</td>\n",
       "      <td>12852</td>\n",
       "      <td>5:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160526</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.234043</td>\n",
       "      <td>0.027792</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.174282</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134721</th>\n",
       "      <td>2761603</td>\n",
       "      <td>3621</td>\n",
       "      <td>4823</td>\n",
       "      <td>20:5</td>\n",
       "      <td>3</td>\n",
       "      <td>20160606</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.081267</td>\n",
       "      <td>0.068635</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.041830</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10203</th>\n",
       "      <td>6092712</td>\n",
       "      <td>5256</td>\n",
       "      <td>5928</td>\n",
       "      <td>30:5</td>\n",
       "      <td>0</td>\n",
       "      <td>20160521</td>\n",
       "      <td>20160523</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.286432</td>\n",
       "      <td>0.240506</td>\n",
       "      <td>49.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.836735</td>\n",
       "      <td>0.692308</td>\n",
       "      <td>0.439024</td>\n",
       "      <td>0.662396</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209055</th>\n",
       "      <td>5542881</td>\n",
       "      <td>1469</td>\n",
       "      <td>7430</td>\n",
       "      <td>50:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160521</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.092346</td>\n",
       "      <td>0.022431</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.016961</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "166704  2266960         5717      8192          20:5        1      20160525   \n",
       "183019  1748229         4579     12852           5:1        0      20160526   \n",
       "134721  2761603         3621      4823          20:5        3      20160606   \n",
       "10203   6092712         5256      5928          30:5        0      20160521   \n",
       "209055  5542881         1469      7430         50:20        1      20160521   \n",
       "\n",
       "            Date  discount_type  discount_rate  discount_man    ...      \\\n",
       "166704      null              1       0.750000            20    ...       \n",
       "183019      null              1       0.800000             5    ...       \n",
       "134721      null              1       0.750000            20    ...       \n",
       "10203   20160523              1       0.833333            30    ...       \n",
       "209055      null              1       0.600000            50    ...       \n",
       "\n",
       "        m_coupon_use_rate  m_sale_with_coupon_rate  um_count  um_buy_count  \\\n",
       "166704           0.021105                 0.052307       1.0           0.0   \n",
       "183019           0.234043                 0.027792       1.0           1.0   \n",
       "134721           0.081267                 0.068635       0.0           0.0   \n",
       "10203            0.286432                 0.240506      49.0          41.0   \n",
       "209055           0.092346                 0.022431       0.0           0.0   \n",
       "\n",
       "        um_coupon_count  um_buy_with_coupon  um_buy_rate  um_coupon_use_rate  \\\n",
       "166704              1.0                 0.0     0.000000            0.000000   \n",
       "183019              0.0                 0.0     1.000000            0.000000   \n",
       "134721              0.0                 0.0     0.000000            0.000000   \n",
       "10203              26.0                18.0     0.836735            0.692308   \n",
       "209055              0.0                 0.0     0.000000            0.000000   \n",
       "\n",
       "        um_buy_with_coupon_rate  pred_prob  \n",
       "166704                 0.000000   0.025477  \n",
       "183019                 0.000000   0.174282  \n",
       "134721                 0.000000   0.041830  \n",
       "10203                  0.439024   0.662396  \n",
       "209055                 0.000000   0.016961  \n",
       "\n",
       "[5 rows x 50 columns]"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# valid predict\n",
    "y_valid_pred = model.predict_proba(validSub[predictors])\n",
    "validSub1 = validSub.copy()\n",
    "validSub1['pred_prob'] = y_valid_pred[:, 1]\n",
    "validSub1.head(5)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计算 AUC："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.616803899905\n"
     ]
    }
   ],
   "source": [
    "# avgAUC calculation\n",
    "vg = validSub1.groupby(['Coupon_id'])\n",
    "aucs = []\n",
    "for i in vg:\n",
    "    tmpdf = i[1] \n",
    "    if len(tmpdf['label'].unique()) != 2:\n",
    "        continue\n",
    "    fpr, tpr, thresholds = roc_curve(tmpdf['label'], tmpdf['pred_prob'], pos_label=1)\n",
    "    aucs.append(auc(fpr, tpr))\n",
    "print(np.average(aucs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 集成模型 LightGBM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 训练"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "LGBMClassifier(boosting_type='gbdt', class_weight=None, colsample_bytree=0.7,\n",
       "        early_stop=50, importance_type='split', learning_rate=0.01,\n",
       "        max_depth=5, metric='logloss', min_child_samples=20,\n",
       "        min_child_weight=0.001, min_split_gain=0.0, n_estimators=5000,\n",
       "        n_jobs=-1, num_leaves=3, objective='binary', random_state=None,\n",
       "        reg_alpha=0.0, reg_lambda=0.0, silent=True, sub_feature=0.7,\n",
       "        subsample=1.0, subsample_for_bin=200000, subsample_freq=0,\n",
       "        verbose=-1)"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "model = lgb.LGBMClassifier(\n",
    "                    learning_rate = 0.01,\n",
    "                    boosting_type = 'gbdt',\n",
    "                    objective = 'binary',\n",
    "                    metric = 'logloss',\n",
    "                    max_depth = 5,\n",
    "                    sub_feature = 0.7,\n",
    "                    num_leaves = 3,\n",
    "                    colsample_bytree = 0.7,\n",
    "                    n_estimators = 5000,\n",
    "                    early_stop = 50,\n",
    "                    verbose = -1)\n",
    "model.fit(trainSub[predictors], trainSub['label'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 验证\n",
    "\n",
    "对验证集中每个优惠券预测的结果计算 AUC，再对所有优惠券的 AUC 求平均。计算 AUC 的时候，如果 label 只有一类，就直接跳过，因为 AUC 无法计算。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Merchant_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Discount_rate</th>\n",
       "      <th>Distance</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>Date</th>\n",
       "      <th>discount_type</th>\n",
       "      <th>discount_rate</th>\n",
       "      <th>discount_man</th>\n",
       "      <th>...</th>\n",
       "      <th>m_coupon_use_rate</th>\n",
       "      <th>m_sale_with_coupon_rate</th>\n",
       "      <th>um_count</th>\n",
       "      <th>um_buy_count</th>\n",
       "      <th>um_coupon_count</th>\n",
       "      <th>um_buy_with_coupon</th>\n",
       "      <th>um_buy_rate</th>\n",
       "      <th>um_coupon_use_rate</th>\n",
       "      <th>um_buy_with_coupon_rate</th>\n",
       "      <th>pred_prob</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>166704</th>\n",
       "      <td>2266960</td>\n",
       "      <td>5717</td>\n",
       "      <td>8192</td>\n",
       "      <td>20:5</td>\n",
       "      <td>1</td>\n",
       "      <td>20160525</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.021105</td>\n",
       "      <td>0.052307</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.018569</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>183019</th>\n",
       "      <td>1748229</td>\n",
       "      <td>4579</td>\n",
       "      <td>12852</td>\n",
       "      <td>5:1</td>\n",
       "      <td>0</td>\n",
       "      <td>20160526</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>0.234043</td>\n",
       "      <td>0.027792</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.082810</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134721</th>\n",
       "      <td>2761603</td>\n",
       "      <td>3621</td>\n",
       "      <td>4823</td>\n",
       "      <td>20:5</td>\n",
       "      <td>3</td>\n",
       "      <td>20160606</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.750000</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>0.081267</td>\n",
       "      <td>0.068635</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.021860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10203</th>\n",
       "      <td>6092712</td>\n",
       "      <td>5256</td>\n",
       "      <td>5928</td>\n",
       "      <td>30:5</td>\n",
       "      <td>0</td>\n",
       "      <td>20160521</td>\n",
       "      <td>20160523</td>\n",
       "      <td>1</td>\n",
       "      <td>0.833333</td>\n",
       "      <td>30</td>\n",
       "      <td>...</td>\n",
       "      <td>0.286432</td>\n",
       "      <td>0.240506</td>\n",
       "      <td>49.0</td>\n",
       "      <td>41.0</td>\n",
       "      <td>26.0</td>\n",
       "      <td>18.0</td>\n",
       "      <td>0.836735</td>\n",
       "      <td>0.692308</td>\n",
       "      <td>0.439024</td>\n",
       "      <td>0.760965</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>209055</th>\n",
       "      <td>5542881</td>\n",
       "      <td>1469</td>\n",
       "      <td>7430</td>\n",
       "      <td>50:20</td>\n",
       "      <td>1</td>\n",
       "      <td>20160521</td>\n",
       "      <td>null</td>\n",
       "      <td>1</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>50</td>\n",
       "      <td>...</td>\n",
       "      <td>0.092346</td>\n",
       "      <td>0.022431</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.013496</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 50 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        User_id  Merchant_id Coupon_id Discount_rate Distance Date_received  \\\n",
       "166704  2266960         5717      8192          20:5        1      20160525   \n",
       "183019  1748229         4579     12852           5:1        0      20160526   \n",
       "134721  2761603         3621      4823          20:5        3      20160606   \n",
       "10203   6092712         5256      5928          30:5        0      20160521   \n",
       "209055  5542881         1469      7430         50:20        1      20160521   \n",
       "\n",
       "            Date  discount_type  discount_rate  discount_man    ...      \\\n",
       "166704      null              1       0.750000            20    ...       \n",
       "183019      null              1       0.800000             5    ...       \n",
       "134721      null              1       0.750000            20    ...       \n",
       "10203   20160523              1       0.833333            30    ...       \n",
       "209055      null              1       0.600000            50    ...       \n",
       "\n",
       "        m_coupon_use_rate  m_sale_with_coupon_rate  um_count  um_buy_count  \\\n",
       "166704           0.021105                 0.052307       1.0           0.0   \n",
       "183019           0.234043                 0.027792       1.0           1.0   \n",
       "134721           0.081267                 0.068635       0.0           0.0   \n",
       "10203            0.286432                 0.240506      49.0          41.0   \n",
       "209055           0.092346                 0.022431       0.0           0.0   \n",
       "\n",
       "        um_coupon_count  um_buy_with_coupon  um_buy_rate  um_coupon_use_rate  \\\n",
       "166704              1.0                 0.0     0.000000            0.000000   \n",
       "183019              0.0                 0.0     1.000000            0.000000   \n",
       "134721              0.0                 0.0     0.000000            0.000000   \n",
       "10203              26.0                18.0     0.836735            0.692308   \n",
       "209055              0.0                 0.0     0.000000            0.000000   \n",
       "\n",
       "        um_buy_with_coupon_rate  pred_prob  \n",
       "166704                 0.000000   0.018569  \n",
       "183019                 0.000000   0.082810  \n",
       "134721                 0.000000   0.021860  \n",
       "10203                  0.439024   0.760965  \n",
       "209055                 0.000000   0.013496  \n",
       "\n",
       "[5 rows x 50 columns]"
      ]
     },
     "execution_count": 61,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# valid set performance \n",
    "y_valid_pred = model.predict_proba(validSub[predictors])\n",
    "validSub1 = validSub.copy()\n",
    "validSub1['pred_prob'] = y_valid_pred[:, 1]\n",
    "validSub1.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计算 AUC："
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0.630400363653\n"
     ]
    }
   ],
   "source": [
    "vg = validSub1.groupby(['Coupon_id'])\n",
    "aucs = []\n",
    "for i in vg:\n",
    "    tmpdf = i[1] \n",
    "    if len(tmpdf['label'].unique()) != 2:\n",
    "        continue\n",
    "    fpr, tpr, thresholds = roc_curve(tmpdf['label'], tmpdf['pred_prob'], pos_label=1)\n",
    "    aucs.append(auc(fpr, tpr))\n",
    "print(np.average(aucs))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 测试并提交成绩"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style>\n",
       "    .dataframe thead tr:only-child th {\n",
       "        text-align: right;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>User_id</th>\n",
       "      <th>Coupon_id</th>\n",
       "      <th>Date_received</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4129537</td>\n",
       "      <td>9983</td>\n",
       "      <td>20160712</td>\n",
       "      <td>0.014623</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>6949378</td>\n",
       "      <td>3429</td>\n",
       "      <td>20160706</td>\n",
       "      <td>0.143826</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2166529</td>\n",
       "      <td>6928</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0.006144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2166529</td>\n",
       "      <td>1808</td>\n",
       "      <td>20160727</td>\n",
       "      <td>0.006103</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>6172162</td>\n",
       "      <td>6500</td>\n",
       "      <td>20160708</td>\n",
       "      <td>0.091049</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   User_id  Coupon_id  Date_received     label\n",
       "0  4129537       9983       20160712  0.014623\n",
       "1  6949378       3429       20160706  0.143826\n",
       "2  2166529       6928       20160727  0.006144\n",
       "3  2166529       1808       20160727  0.006103\n",
       "4  6172162       6500       20160708  0.091049"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# test prediction for submission\n",
    "y_test_pred = model.predict_proba(test[predictors])\n",
    "submit = test[['User_id','Coupon_id','Date_received']].copy()\n",
    "submit['label'] = y_test_pred[:,1]\n",
    "submit.to_csv('submit2.csv', index=False, header=False)\n",
    "submit.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 保存模型 & 导入模型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "if not os.path.isfile('2_model.pkl'):\n",
    "    with open('2_model.pkl', 'wb') as f:\n",
    "        pickle.dump(model, f)\n",
    "else:\n",
    "    with open('2_model.pkl', 'rb') as f:\n",
    "        model = pickle.load(f)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "## 优化模型..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- **特征工程**\n",
    "\n",
    "- **机器学习算法**\n",
    "\n",
    "- **模型集成**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 参考代码"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "[比赛第一名代码与解析](https://github.com/wepe/O2O-Coupon-Usage-Forecast)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
